﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using UnityScriptEngine;

namespace EnrollmentSystem
{
    public partial class frmStudentProfileManager : Form
    {
        UnityProcessor proc = new UnityProcessor();
        Threader t = new Threader();
        UnityScript[] dat = new UnityScript[0];

        //Operation Attributes
        bool AddNew = false;
        int curEdit = -1;
        UnityElement curData;
        string prevSrc = "";
        public frmStudentProfileManager()
        {
            InitializeComponent();
            InitializeFrameSliderForm();
        }
        private void InitializeForm()
        {
            DoEvents();

            DetailControl(true);
            grpDetails.Visible = false;
            LoadStudentProfile();
        }
        void DoEvents()
        {
            //GUI Events
            btnAdd.Click += new EventHandler(delegate
            {
                GenerateStudentID();
            });
            btnCancel.Click += new EventHandler(delegate
            {
                
                AddNew = false;
                DetailControl(false);
                foreach (Control ctl in grpDetails.Controls)
                {
                    if (ctl is TextBox) ((TextBox)ctl).Text = "";
                    if (ctl is ComboBox) ((ComboBox)ctl).SelectedIndex = -1;
                    if (ctl is DateTimePicker) ((DateTimePicker)ctl).Value = DateTime.Now;
                }
            });
            btnSave.Click += new EventHandler(delegate
            {
                SaveStudentProfile();
                grpDetails.Enabled = false;
              
            });
            btnDelete.Click += new EventHandler(delegate
            {
                if (MessageBox.Show("Do you wish to continue?",
                    "Delete User",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) return;
                DeleteStudentProfile();
                grpDetails.Enabled = false;
            });
            grdItems.DoubleClick += new EventHandler(delegate
            {
                curEdit = grdItems.SelectedRows[0].Index;

                dat[1].Data_Get(curEdit, out curData);

                txtIDNo.Text = curData.Data_GetField(0).ToString();
                txtName.Text = curData.Data_GetField(1).ToString();
                txtCourse.Text = curData.Data_GetField(2).ToString();
                txtDept.Text = curData.Data_GetField(3).ToString();
                txtBirthDate.Value = DateTime.Parse(curData.Data_GetField(4).ToString());
                txtSex.Text = curData.Data_GetField(5).ToString();
                txtContactNo.Text = curData.Data_GetField(6).ToString();
                txtAddress.Text = curData.Data_GetField(7).ToString();
                txtBPlace.Text = curData.Data_GetField(8).ToString();
                txtSecSchool.Text = curData.Data_GetField(9).ToString();
                txtYrGraduated.Text = curData.Data_GetField(10).ToString();

                btnDelete.Enabled = true;
                DetailControl(true);
                AddNew = false;
            });
            txtSearch.KeyPress += new KeyPressEventHandler(delegate(object sender, KeyPressEventArgs e)
            {
                if (e.KeyChar == (char)13)
                {
                    txtPage.Value = 1m;
                    prevSrc = txtSearch.Text;
                    LoadStudentProfile();
                }
            });
            txtPage.ValueChanged += new EventHandler(delegate
            {
                LoadStudentProfile();
            });
            //UnityScriptEngine Events
            proc.ProcessCompleted += new ProcessHandler(proc_ProcessCompleted);
            proc.ProcessError += new ProcessHandler(proc_ProcessError);
        }
        //UnityScriptEngine
        void proc_ProcessError(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                MessageBox.Show(desc);
                switch (id)
                {
                    case "LOADUSERS":
                        DetailControl(true);
                        grpDetails.Enabled = false;
                        break;
                    case "DELETEUSER":
                    case "SAVEUSER":
                        DetailControl(true);
                        break;
                }
            });
        }
        void proc_ProcessCompleted(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                switch (id)
                {
                    case "DELETEUSER":
                    case "SAVEUSER":
                    case "LOADUSERS":
                        proc.DBPUX_Data_Fetch(out dat);
                        grdItems.DataSource = dat[0].ToDataTable();
                        if (dat[0].Data_Count == 50)
                            txtPage.Maximum = txtPage.Value + 1;
                        else
                            txtPage.Maximum = txtPage.Value;

                        txtCourse.Items.Clear();
                        txtCourse.Items.AddRange(dat[2].ToList(0));
                        txtDept.Items.Clear();
                        txtDept.Items.AddRange(dat[3].ToList(0));
                        txtSex.Items.Clear();
                        txtSex.Items.AddRange(dat[4].ToList(0));


                        AddNew = false;
                        DetailControl(false);
                        foreach (Control ctl in grpDetails.Controls)
                        {
                            if (ctl is TextBox) ((TextBox)ctl).Text = "";
                        }
                        FormatGrid();
                        break;
                    case "GENERATEID":
                        UnityScript[] ugen;
                        UnityElement egen;
                        proc.DBPUX_Data_Fetch(out ugen);
                        ugen[0].Data_Get(0, out egen);
                        txtIDNo.Text = "ST" + int.Parse(egen.Data_GetField(0).ToString()).ToString("000000000");
                        
                        txtCourse.Items.Clear();
                        txtCourse.Items.AddRange(ugen[1].ToList(0));
                        txtDept.Items.Clear();
                        txtDept.Items.AddRange(ugen[2].ToList(0));
                        txtSex.Items.Clear();
                        txtSex.Items.AddRange(ugen[3].ToList(0));

                        //Proper Add New GUI
                        AddNew = true;
                        btnDelete.Enabled = false;
                        DetailControl(true);
                        break;
                }
            });
        }
        //Data Operations
        void LoadStudentProfile()
        {
            UnityScript u = new UnityScript("LOADUSERS", 5);
            u.Script_Add(Script.DBPUX_BEGIN);
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'ID Number', fName AS 'Name', fCourse AS 'Course', " +
                "fDepartment AS 'Department', fSex AS 'Sex', fStamp FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
                "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
                "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
                "LIMIT {1}, 50",
                prevSrc,
                (txtPage.Value - 1m) * 50
                ));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
                "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
                "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
                "LIMIT {1}, 50",
                prevSrc,
                (txtPage.Value - 1m) * 50
                ));

            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='COURSES_OFFERED'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='DEPARTMENTS'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='SEX'");
            u.Script_Add(Script.DBPUX_COMMIT);

            proc.Run(u);
        }
        void SaveStudentProfile()
        {
            UnityScript u = new UnityScript("SAVEUSER", 5);
            //u.Script_Add(Script.DBPUX_OPEN);
            u.Script_Add(Script.DBPUX_BEGIN);
            if (AddNew)
            {
                u.Script_Add(Script.DBPUX_INSERT, "tblstudentprofile(fNumber, fName, fCourse, fDepartment, fBirthDate, fSex, fContactNumber, fAddress, fPlaceOfBirth, fSecondarySchool, fYearGraduated)",
                    new string[]{
                        txtIDNo.Text,
                        txtName.Text,
                        txtCourse.Text,
                        txtDept.Text,
                        txtBirthDate.Value.ToString("yyyy-MM-dd"),
                        txtSex.Text,
                        txtContactNo.Text,
                        txtAddress.Text,
                        txtBPlace.Text,
                        txtSecSchool.Text,
                        txtYrGraduated.Text
                    });
                u.Script_Add(Script.DBPUX_UPDATE, "tblgenerator SET fNum=fNum+1", "(fSeries='ST')");
            }
            else
            {
                u.Script_Add(Script.DBPUX_UPDATE, string.Format("tblstudentprofile SET " +
                    "fNumber='{0}', fName='{1}', fCourse='{2}', fDepartment='{3}', fBirthDate='{4}', fSex='{5}', fContactNumber='{6}', fAddress='{7}', fPlaceOfBirth='{8}', fSecondarySchool='{9}', fYearGraduated='{10}'",
                    txtIDNo.Text,
                    txtName.Text,
                    txtCourse.Text,
                    txtDept.Text,
                    txtBirthDate.Value.ToString("yyyy-MM-dd"),
                    txtSex.Text,
                    txtContactNo.Text,
                    txtAddress.Text,
                    txtBPlace.Text,
                    txtSecSchool.Text,
                    txtYrGraduated.Text
                    ),
                    string.Format("(fID='{0}') AND (fStamp='{1}')", curData.Data_ID.ToString(), curData.Data_Stamp));
            }
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'ID Number', fName AS 'Name', fCourse AS 'Course', " +
               "fDepartment AS 'Department', fSex AS 'Sex', fStamp FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
               "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
               "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
               "LIMIT {1}, 50",
               prevSrc,
               (txtPage.Value - 1m) * 50
               ));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
                "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
                "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
                "LIMIT {1}, 50",
                prevSrc,
                (txtPage.Value - 1m) * 50
                ));
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='COURSES_OFFERED'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='DEPARTMENTS'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='SEX'");
            u.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(u);
        }
        void DeleteStudentProfile()
        {
            UnityScript u = new UnityScript("DELETEUSER", 5);
            u.Script_Add(Script.DBPUX_BEGIN);

            u.Script_Add(Script.DBPUX_DELETE, "tblstudentprofile",
                string.Format("(fID='{0}') AND (fStamp='{1}')", curData.Data_ID.ToString(), curData.Data_Stamp));

            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'ID Number', fName AS 'Name', fCourse AS 'Course', " +
               "fDepartment AS 'Department', fSex AS 'Sex', fStamp FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
               "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
               "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
               "LIMIT {1}, 50",
               prevSrc,
               (txtPage.Value - 1m) * 50
               ));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT * FROM tblstudentprofile WHERE fNumber LIKE '%{0}%' OR fName LIKE '%{0}%' OR fCourse LIKE '%{0}%' OR " +
                "fDepartment LIKE '%{0}%' OR fBirthDate LIKE '%{0}%' OR fSex LIKE '%{0}%' OR fContactNumber LIKE '%{0}%' OR " +
                "fAddress LIKE '%{0}%' ORDER BY fName ASC, fNumber ASC " +
                "LIMIT {1}, 50",
                prevSrc,
                (txtPage.Value - 1m) * 50
                ));
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='COURSES_OFFERED'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='DEPARTMENTS'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='SEX'");
            u.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(u);
        }
        void GenerateStudentID()
        {
            UnityScript u = new UnityScript("GENERATEID", 5);
            u.Script_Add(Script.DBPUX_BEGIN);
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fNum, fStamp FROM tblgenerator WHERE fSeries='ST'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='COURSES_OFFERED'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='DEPARTMENTS'");
            u.Script_Add(Script.DBPUX_QUERY, "SELECT fID, fData ,fStamp FROM tbllookup WHERE fKey='SEX'");
            u.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(u);
        }
        //GUI Operations
        void DetailControl(bool s)
        {
            grdItems.Enabled = !s;
            grpDetails.Visible = s;
            grpDetails.Enabled = s;
            btnAdd.Enabled = !s;
            txtSearch.Enabled = !s;
            txtPage.Enabled = !s;
        }
        void FormatGrid()
        {
            foreach (DataGridViewColumn col in grdItems.Columns)
            {
                col.Width = (grdItems.Width / grdItems.Columns.Count) - 10;
                col.SortMode = DataGridViewColumnSortMode.NotSortable;
            }
        }
    }
}
